
with tab_tab_end_piece as (
    select
        id
         ,waybill_no
         ,end_code
         ,end_code_desc
         ,source
         ,input_time
         ,network_id
         ,network_code
         ,network_name
         ,agency_network_id
         ,agency_network_code
         ,agency_network_name
         ,league_network_id
         ,league_network_code
         ,league_network_name
         ,is_delete
         ,end_time
         ,update_time
         ,sender_network_id
         ,sender_network_code
         ,sender_network_name
         ,scan_by              as  scan_user_id        --扫描员ID
         ,scan_by_code         as  scan_user_code      --扫描员编码
         ,scan_by_name         as  scan_user_name      --扫描员名称
         ,row_number() over(partition by id order by update_time desc) as row_id
         ,date_format(input_time,'yyyy-MM-dd') as dt
    from jms_ods.tab_end_piece_di
    where dt >= date_add('{{ execution_date | cst_ds }}', -90)
    and dt <= date_add('{{ execution_date | cst_ds }}', 0)
)

insert overwrite table jms_dwd.dwd_tab_end_piece_base_dt partition(dt)
select
    id
     ,waybill_no
     ,end_code
     ,end_code_desc
     ,source
     ,input_time
     ,network_id
     ,network_code
     ,network_name
     ,agency_network_id
     ,agency_network_code
     ,agency_network_name
     ,league_network_id
     ,league_network_code
     ,league_network_name
     ,is_delete
     ,end_time
     ,update_time
     ,sender_network_id
     ,sender_network_code
     ,sender_network_name
     ,scan_user_id        --扫描员ID
     ,scan_user_code      --扫描员编码
     ,scan_user_name      --扫描员名称
     ,dt
from tab_tab_end_piece
where dt >= date_add('{{ execution_date | cst_ds }}', -90)
and dt <= date_add('{{ execution_date | cst_ds }}', 0)
and row_id = 1
distribute by dt;